capture log close
clear all
set maxvar 50000
set more off
pause off
# delimit;

local crime "";
local output "";

/* 
Table B8
RD and IV estimates of criminal justice outcomes in each time period
*/


use "`crime'/prwora_crime_summstats.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_all "a" ;


preserve ;

		keep	`pre_all'avg_1onssi* `pre_all'avg_2onssi* `pre_all'avg_3onssi* `pre_all'avg_4onssi*
				`pre_all'n_1crime2genr* `pre_all'n_2crime2genr* `pre_all'n_3crime2genr* `pre_all'n_4crime2genr* 
				`pre_all'n_1crime2ahinc* `pre_all'n_2crime2ahinc* `pre_all'n_3crime2ahinc* `pre_all'n_4crime2ahinc* 
				`pre_all'n_1crime2shinc* `pre_all'n_2crime2shinc* `pre_all'n_3crime2shinc* `pre_all'n_4crime2shinc* 				
				;

		xpose, clear varname ;

		rename v1 mean ;
		rename _varname variables ;
		order variables mean ;

		replace variables = subinstr(variables,"_1997_2017", "", .) ;
		replace variables = subinstr(variables,"`pre_all'n_1crime2", "1_", .) ;
		replace variables = subinstr(variables,"`pre_all'n_2crime2", "2_", .) ;
		replace variables = subinstr(variables,"`pre_all'n_3crime2", "3_", .) ;
		replace variables = subinstr(variables,"`pre_all'n_4crime2", "4_", .) ;
		replace variables = subinstr(variables,"`pre_all'avg_1", "1_", .) ;
		replace variables = subinstr(variables,"`pre_all'avg_2", "2_", .) ;
		replace variables = subinstr(variables,"`pre_all'avg_3", "3_", .) ;
		replace variables = subinstr(variables,"`pre_all'avg_4", "4_", .) ;

		tempfile all_mean ;
		save `all_mean' ;

restore ;



clear;


foreach method in rd iv {;
			use "`crime'/prwora_bw264_crime_poly1allcov_`method'_all.dta" ;
			
			#delim;
			gen n_1crime2 = strpos(variables, "n_1crime2") ;
			gen n_2crime2 = strpos(variables, "n_2crime2") ;
			gen n_3crime2 = strpos(variables, "n_3crime2") ;
			gen n_4crime2 = strpos(variables, "n_4crime2") ;

			keep if n_1crime2 == 1 | n_2crime2 == 1 | n_3crime2 == 1 | n_4crime2 == 1 | variables == "avg_1onssi_1997_2017"  | variables == "avg_2onssi_1997_2017"  | variables == "avg_3onssi_1997_2017"  | variables == "avg_4onssi_1997_2017" ;
			
			if "`method'" == "rd" {;

				keep variables n_post sen_post N ;
			
			};
			
			else {;
			
				keep if n_1crime2 == 1 | n_2crime2 == 1 | n_3crime2 == 1 | n_4crime2 == 1 ;
				
				keep variables n_age18unfav sen_age18unfav N;
			};

			replace variables = subinstr(variables,"_1997_2017", "", .) ;
			replace variables = subinstr(variables,"n_1crime2", "1_", .) ;
			replace variables = subinstr(variables,"n_2crime2", "2_", .) ;
			replace variables = subinstr(variables,"n_3crime2", "3_", .) ;
			replace variables = subinstr(variables,"n_4crime2", "4_", .) ;			
			replace variables = subinstr(variables,"avg_1", "1_", .) ;
			replace variables = subinstr(variables,"avg_2", "2_", .) ;
			replace variables = subinstr(variables,"avg_3", "3_", .) ;
			replace variables = subinstr(variables,"avg_4", "4_", .) ;

			#delim;

			if "`method'" == "rd" {;
			
			local crime "1_onssi 2_onssi 3_onssi 4_onssi
			1_genr 2_genr 3_genr 4_genr 1_ahinc 2_ahinc 3_ahinc 4_ahinc
			1_shinc 2_shinc 3_shinc 4_shinc" ;

			};
			
			else {;
			
				local crime "1_genr 2_genr 3_genr 4_genr
				1_ahinc 2_ahinc 3_ahinc 4_ahinc 
				1_shinc 2_shinc 3_shinc 4_shinc" ;

			
			};
						 
			gen keep = strpos(variables, "char") ;

			foreach var in `crime' {;

				replace keep = strpos(variables, "`var'") if keep == 0;

			};

			drop if keep == 0 ;
			drop keep ;


			merge 1:1 variables using `all_mean' ;
			drop _merge ;

			gen flag_int = strpos(variables, "t") ;
			gen length = length(variables) ;
			replace flag_int = 1 if flag_int != 0 & length == 7 ;
			replace flag_int = 0 if flag_int != 1 ;
			replace flag_int = 0 if strpos(variables, "ahinc") ;
			replace flag_int = 1 if strpos(variables, "shinc") ;
			drop if strpos(variables, "char") ;
			
			replace variables = "On SSI (1997-01)" if variables == "1_onssi" ;
			replace variables = "On SSI (2002-06)" if variables == "2_onssi" ;
			replace variables = "On SSI (2007-12)" if variables == "3_onssi" ;
			replace variables = "On SSI (2013-17)" if variables == "4_onssi" ;
		
			replace variables = "Income-generating charge (1997-01)" if variables == "1_genr" | variables == "1_genrt" ;
			replace variables = "Income-generating charge (2002-06)" if variables == "2_genr" | variables == "2_genrt" ;
			replace variables = "Income-generating charge (2007-12)" if variables == "3_genr" | variables == "3_genrt" ;
			replace variables = "Income-generating charge (2013-17)" if variables == "4_genr" | variables == "4_genrt" ;
			
			replace variables = "Incarcerated (annual likelihood/days) (1997-01)" if variables == "1_ahinc" | variables == "1_shinc" ;
			replace variables = "Incarcerated (annual likelihood/days) (2002-06)" if variables == "2_ahinc" | variables == "2_shinc" ;
			replace variables = "Incarcerated (annual likelihood/days) (2007-12)" if variables == "3_ahinc" | variables == "3_shinc" ;			
			replace variables = "Incarcerated (annual likelihood/days) (2013-17)" if variables == "4_ahinc" | variables == "4_shinc" ;			

			
			sort variables flag_int ;

			if "`method'" == "rd" {;

				rename n_post pt_est_rd ;
				rename sen_post se_rd ;
				gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
				gen sta_sign_rd = "" ;
				replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
				replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
				replace sta_sign_rd = "***" if star_rd <= 0.01 ;
				drop star_rd ;
			};
			
			else {;
				
				rename n_age18unfav pt_est_iv ;
				rename sen_age18unfav se_iv ;
				gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
				gen sta_sign_iv = "" ;
				replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
				replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
				replace sta_sign_iv = "***" if star_iv <= 0.01 ;
				drop star_iv ;
			};

			
			
			order variables pt_est* se* sta_sign* mean N flag_int;
			
			
			tostring flag_int, replace ;
			
			replace flag_int = "_in" if flag_int == "1" ;
			replace flag_int = "_ex" if flag_int == "0" ;
			
			drop length ;
			
			reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;
	
			tostring se*, replace ;
			replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
			replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
			tempfile all_`method'_all ;
			save `all_`method'_all' ;
			
			
}; /* method */
		

use `all_rd_all', clear ;
		
merge 1:1 variables using `all_iv_all' ;
drop _merge ;
		
		
order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
				pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
gen order = 1 if variables == "On SSI (1997-01)" ;
replace order = 2 if variables == "Income-generating charge (1997-01)" ;
replace order = 3 if variables == "Incarcerated (annual likelihood/days) (1997-01)" ;
replace order = 4 if variables == "On SSI (2002-06)" ;
replace order = 5 if variables == "Income-generating charge (2002-06)" ;
replace order = 6 if variables == "Incarcerated (annual likelihood/days) (2002-06)" ;
replace order = 7 if variables == "On SSI (2007-12)" ;
replace order = 8 if variables == "Income-generating charge (2007-12)" ;
replace order = 9 if variables == "Incarcerated (annual likelihood/days) (2007-12)" ;		
replace order = 10 if variables == "On SSI (2013-17)" ;
replace order = 11 if variables == "Income-generating charge (2013-17)" ;
replace order = 12 if variables == "Incarcerated (annual likelihood/days) (2013-17)" ;
	
sort order ;
		
drop order ;
	
export excel using "`output'/tabB8.xls", firstrow(variables) ;		
